USE master;
GO

SET NOCOUNT ON;
GO

PRINT 'active I/O issues';
SELECT
	session_id,
    wait_duration_ms,
    wait_type,
    resource_description
FROM sys.dm_os_waiting_tasks WITH (NOLOCK)
WHERE wait_type LIKE N'PAGEIOLATCH%' OR
	wait_type IN (N'IO_COMPLETION', N'WRITELOG', N'ASYNC_IO_COMPLETION') OPTION (RECOMPILE);

PRINT 'waiting tasks';
SELECT 
	wt.session_id, 
    wt.exec_context_id, 
    wt.wait_duration_ms,
	er.cpu_time AS cpu_time_ms,
    wt.wait_type, 
    wt.blocking_session_id, 
    wt.resource_description, 
	CASE wt.wait_type 
		WHEN N'CXPACKET' THEN 
			RIGHT (wt.resource_description, 
            CHARINDEX (N'=', REVERSE (wt.resource_description)) - 1) 
        ELSE NULL 
    END AS node_id, 
    er.database_id, 
	es.[program_name], 
	st.[text] AS query_text, 
    qp.query_plan
FROM sys.dm_os_waiting_tasks wt WITH (NOLOCK)
INNER JOIN sys.dm_exec_sessions es WITH (NOLOCK) ON 
	wt.session_id = es.session_id 
INNER JOIN sys.dm_exec_requests er WITH (NOLOCK) ON 
    es.session_id = er.session_id 
OUTER APPLY sys.dm_exec_sql_text (er.[sql_handle]) st 
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) qp 
WHERE es.is_user_process = 1 
ORDER BY wt.session_id ASC, wt.exec_context_id ASC OPTION (RECOMPILE);

PRINT 'memory grants information';
SELECT
	session_id,
    request_id,
    scheduler_id,
    dop,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    query_cost,
    timeout_sec,
    resource_semaphore_id,
    queue_id,
    wait_order,
    is_next_candidate,
    wait_time_ms,
    plan_handle,
    sql_handle,
    group_id,
    pool_id,
    is_small,
    ideal_memory_kb
FROM sys.dm_exec_query_memory_grants WITH (NOLOCK) OPTION (RECOMPILE);